from flask import Blueprint, render_template, jsonify, request
from python.extensions import db
from python.model.weather import WeatherData
from sqlalchemy import func
import datetime

# 创建月度空气质量趋势分析蓝图，URL前缀为/menu
air_quality_trend_bp = Blueprint('air_quality_trend', __name__, url_prefix='/menu')


@air_quality_trend_bp.route('/air_quality_trend', methods=['GET'])
def trend():
    """渲染月度空气质量趋势页面"""
    # 获取所有城市
    cities = db.session.query(WeatherData.city).distinct().all()
    cities = [city[0] for city in cities]

    # 获取所有年份 - 使用MySQL的YEAR()函数
    years = db.session.query(func.year(WeatherData.date)).distinct().all()
    years = [str(year[0]) for year in years]  # 转换为字符串
    years.sort(reverse=True)  # 按年份降序排列

    # 默认选择第一个城市
    initial_urban = cities[0] if cities else ""

    return render_template(
        'menu/air_quality_trend.html',  # 模板路径为menu/air_quality_trend.html
        cities=cities,
        years=years,
        urban=initial_urban
    )


@air_quality_trend_bp.route('/monthly_air_quality', methods=['GET'])
def monthly_air_quality():
    """提供月度平均空气质量数据"""
    urban = request.args.get('urban', '')
    year = request.args.get('year', '')

    if not urban:
        return jsonify({"error": "城市参数不能为空"}), 400

    try:
        # 构建查询：使用MySQL的YEAR()、MONTH()和CONCAT()函数处理日期
        month_expr = func.concat(
            func.year(WeatherData.date),
            '-',
            func.lpad(func.month(WeatherData.date), 2, '0')  # 确保月份为两位数字
        )
        query = db.session.query(
            month_expr.label('month'),
            func.avg(WeatherData.aqi).label('avg_aqi')  # 使用aqi作为空气质量指数列名
        ).filter(WeatherData.city == urban)

        # 如果指定了年份，添加年份过滤
        if year:
            query = query.filter(func.year(WeatherData.date) == year)

        # 按年月分组并排序
        query = query.group_by(month_expr).order_by(month_expr)

        # 执行查询
        results = query.all()

        if not results:
            return jsonify({
                "months": [],
                "aqi": [],  # 使用aqi作为返回数据的键名
                "error": "未找到该城市的空气质量数据"
            })

        # 整理数据
        months = [result.month for result in results]
        aqi = [float(result.avg_aqi) for result in results]

        return jsonify({
            "months": months,
            "aqi": aqi
        })

    except Exception as e:
        print(f"获取月度空气质量数据时出错: {e}")
        return jsonify({"error": "获取数据时出错，请稍后重试"}), 500